﻿CREATE PROCEDURE [dbo].[utl_RemoveAuditing]
@Table [sysname]=NULL
AS
BEGIN
	DECLARE @True  BIT; SET @True  = 1
	DECLARE @False BIT; SET @False = 0
	
	DECLARE @SQL                    NVARCHAR(MAX)
	DECLARE @ErrorCode              INT
	DECLARE @RowsAffected           INT
	DECLARE @Message                VARCHAR(MAX)
	DECLARE @DefaultConstraintName  sysname
	
	-- see if table name was passed
	IF @Table IS NULL
	BEGIN
	    SET @Message = 'Must pass table name.'
	    RAISERROR(@Message, 16, 1)
	    RETURN 1
	END
	
	-- see if table exists
	IF dbo.fn_TableExists(@Table) = @False
	BEGIN
	    SET @Message = 'Table ''' + @Table + ''' not found.'
	    RAISERROR(@Message, 16, 1)
	    RETURN 2
	END
	
	-- disable triggers for table
	SET @SQL = 'ALTER TABLE ' + @Table + ' DISABLE TRIGGER ALL'
	EXECUTE sp_executesql @SQL
	SELECT @ErrorCode = @@ERROR,
	       @RowsAffected = @@ROWCOUNT
	
	IF @ErrorCode <> 0
	BEGIN
	    SET @Message = 'Could not disable triggers for table ''' + @Table + '''.'
	    RAISERROR(@Message, 16, 1)
	    RETURN 3
	END
	
	-- remove trigger
	IF dbo.fn_TriggerExists(@Table, @Table + '_Audit_InsUpdDelTrg') = @True
	BEGIN
	    SET @SQL = 'DROP TRIGGER ' + @Table + '_Audit_InsUpdDelTrg'
	    EXECUTE sp_executesql @SQL
	END
	
	SELECT @ErrorCode = @@ERROR,
	       @RowsAffected = @@ROWCOUNT
	
	IF @ErrorCode <> 0
	BEGIN
	    SET @Message = 'Could not drop trigger ''' + @Table + '_Audit_InsUpdDelTrg.'''
	    RAISERROR(@Message, 16, 1)
	    RETURN 4
	END
	
	-- enable triggers for table
	SET @SQL = 'ALTER TABLE ' + @Table + ' ENABLE TRIGGER ALL'
	EXECUTE sp_executesql @SQL
	
	SELECT @ErrorCode = @@ERROR,
	       @RowsAffected = @@ROWCOUNT
	
	IF @ErrorCode <> 0
	BEGIN
	    SET @Message = 'Could not enable triggers for table ''' + @Table + '''.' 
	    RAISERROR(@Message, 16, 1)
	    RETURN 5
	END
	
	RETURN 0
END

